package com.xuwei.utils.db;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

/**
 * 数据库操作助手
 * @author David
 * @since 2016年6月2日 上午9:21:38
 * @version 
 * @since JDK 1.6
 */
public class DBAssist {
	private DataSource ds;
	public DBAssist(DataSource ds){
		this.ds = ds;
	}
	/**
	 * 完成DML语句:记录的添加、删除、修改
	 */
	public void update(String sql,Object[] params){
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try{
			conn = ds.getConnection();//得到连接
			stmt = conn.prepareStatement(sql);
			//设置参数
			ParameterMetaData pmd = stmt.getParameterMetaData();
			int count = pmd.getParameterCount();//得到sql语句中的占位符的个数
			if(count>0){
				//有参数
				if(params==null||params.length<1)
					throw new IllegalArgumentException("The parameter is wrong");
				if(params.length!=count)//参数个数不匹配
					throw new IllegalArgumentException("The parameter count is wrong");
				//参数个数匹配
				for(int i=0;i<count;i++){
					stmt.setObject(i+1, params[i]);
				}
			}
			stmt.executeUpdate();
		}catch(Exception e){
			throw new DBAssistException(e);
		}finally{
			//释放资源
			release(rs, stmt, conn);
		}
	}
	public Object query(String sql,Object[] params,ResultSetHandler rsh){
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try{
			conn = ds.getConnection();//得到连接
			stmt = conn.prepareStatement(sql);
			//设置参数
			ParameterMetaData pmd = stmt.getParameterMetaData();
			int count = pmd.getParameterCount();//得到sql语句中的占位符的个数
			if(count>0){
				//有参数
				if(params==null||params.length<1)
					throw new IllegalArgumentException("The parameter is wrong");
				if(params.length!=count)//参数个数不匹配
					throw new IllegalArgumentException("The parameter count is wrong");
				//参数个数匹配
				for(int i=0;i<count;i++){
					stmt.setObject(i+1, params[i]);
				}
			}
			rs = stmt.executeQuery();
			//目的：封装到对象中。有结果集
			return rsh.handler(rs);
		}catch(Exception e){
			throw new DBAssistException(e);
		}finally{
			//释放资源
			release(rs, stmt, conn);
		}
	}
	private static void release(ResultSet rs,Statement stmt,Connection conn){
		if(rs!=null){
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			rs = null;
		}
		if(stmt!=null){
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			stmt = null;
		}
		if(conn!=null){
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conn = null;
		}
	}
}

/**
 * 异常类
 * @author David
 * @since 2016年6月2日 上午9:21:12
 * @version 
 * @since JDK 1.6
 */
class DBAssistException extends RuntimeException {

	public DBAssistException() {
	}

	public DBAssistException(String message) {
		super(message);
	}

	public DBAssistException(Throwable cause) {
		super(cause);
	}

	public DBAssistException(String message, Throwable cause) {
		super(message, cause);
	}

}

class BeanHandler implements ResultSetHandler {
	private Class clazz;
	public BeanHandler(Class clazz){
		this.clazz = clazz;
	}
	@Override
	public Object handler(ResultSet rs) {
		try {
			Object bean = clazz.newInstance();//目标JavaBean实例
			if(rs.next()){
				ResultSetMetaData rsmd = rs.getMetaData();
				int count = rsmd.getColumnCount();
				for(int i=0;i<count;i++){
					String columnName = rsmd.getColumnName(i+1);
					Object columnValue = rs.getObject(i+1);
					Field f = clazz.getDeclaredField(columnName);
					f.setAccessible(true);
					f.set(bean, columnValue);
				}
				return bean;
			}else
				return null;
		} catch (Exception e) {
			throw new DBAssistException(e);
		}
	}

}

/**
 * 前提：JavaBean中的字段与数据库表中的字段一致
 * 该处理器只适合结果集中只多条记录的情况
 */
class BeanListHandler implements ResultSetHandler {
	private Class clazz;
	public BeanListHandler(Class clazz){
		this.clazz = clazz;
	}
	@Override
	public Object handler(ResultSet rs) {
		List beans = new ArrayList();
		try {
			while(rs.next()){
				Object bean = clazz.newInstance();//目标JavaBean实例
				ResultSetMetaData rsmd = rs.getMetaData();
				int count = rsmd.getColumnCount();
				for(int i=0;i<count;i++){
					String columnName = rsmd.getColumnName(i+1);
					Object columnValue = rs.getObject(i+1);
					Field f = clazz.getDeclaredField(columnName);
					f.setAccessible(true);
					f.set(bean, columnValue);
				}
				beans.add(bean);
			}
			return beans;
		} catch (Exception e) {
			throw new DBAssistException(e);
		}
	}

}

/**
 * 结果集处理接口
 * @author David
 * @since 2016年6月2日 上午9:20:03
 * @version 
 * @since JDK 1.6
 */
interface ResultSetHandler {
	Object handler(ResultSet rs);//把结果集中的数据封装到对象中
}







